		**********************************************************
		** File: cleaning_fh.do									**
		** Paper: Human Trafficking Indicators: A New Dataset	**
		** Author: Richard Frank								**
		** Date: July 11, 2021									**
		** Task: Cleaning Freedom House							**
 		**********************************************************
 
	clear
	version 16

	cd "/~/"
	
	import excel "FIW1973-2018.xlsx", sheet("Country Ratings, Statuses ") clear
	 
	* Renaming the variables *

	* FOR ALL YEARS to 2018 *
	rename B PR1972 
	rename C CL1972
	rename D S1972

	rename E PR1973
	rename F CL1973
	rename G S1973

	rename H PR1974
	rename I CL1974
	rename J S1974

	rename K PR1975
	rename L CL1975
	rename M S1975

	rename N PR1976
	rename O CL1976
	rename P S1976

	rename Q PR1977
	rename R CL1977
	rename S S1977

	rename T PR1978
	rename U CL1978
	rename V S1978

	rename W PR1979
	rename X CL1979
	rename Y S1979

	rename Z PR1980
	rename AA CL1980
	rename AB S1980

	***** Jan.1981-Aug. 1982 ***

	** In the future I think I should replace the 1980 values for 1981. ***

	rename AC PR1982
	rename AD CL1982
	rename AE S1982

	rename AF PR1983
	rename AG CL1983
	rename AH S1983

	rename AI PR1984
	rename AJ CL1984
	rename AK S1984

	rename AL PR1985
	rename AM CL1985
	rename AN S1985

	rename AO PR1986
	rename AP CL1986
	rename AQ S1986

	rename AR PR1987
	rename AS CL1987
	rename AT S1987

	rename AU PR1988
	rename AV CL1988
	rename AW S1988

	rename AX PR1989
	rename AY CL1989
	rename AZ S1989

	rename BA PR1990
	rename BB CL1990
	rename BC S1990

	rename BD PR1991
	rename BE CL1991
	rename BF S1991

	rename BG PR1992
	rename BH CL1992
	rename BI S1992

	rename BJ PR1993
	rename BK CL1993
	rename BL S1993

	rename BM PR1994
	rename BN CL1994
	rename BO S1994

	rename BP PR1995
	rename BQ CL1995
	rename BR S1995

	rename BS PR1996
	rename BT CL1996
	rename BU S1996

	rename BV PR1997
	rename BW CL1997
	rename BX S1997

	rename BY PR1998
	rename BZ CL1998
	rename CA S1998

	rename CB PR1999
	rename CC CL1999
	rename CD S1999

	rename CE PR2000
	rename CF CL2000
	rename CG S2000

	rename CH PR2001
	rename CI CL2001
	rename CJ S2001

	rename CK PR2002
	rename CL CL2002
	rename CM S2002

	rename CN PR2003
	rename CO CL2003
	rename CP S2003

	rename CQ PR2004
	rename CR CL2004
	rename CS S2004

	rename CT PR2005
	rename CU CL2005
	rename CV S2005

	rename CW PR2006
	rename CX CL2006
	rename CY S2006

	rename CZ PR2007
	rename DA CL2007
	rename DB S2007

	rename DC PR2008
	rename DD CL2008
	rename DE S2008

	rename DF PR2009
	rename DG CL2009
	rename DH S2009

	rename DI PR2010
	rename DJ CL2010
	rename DK S2010

	rename DL PR2011
	rename DM CL2011
	rename DN S2011

	rename DO PR2012
	rename DP CL2012
	rename DQ S2012

	rename DR PR2013
	rename DS CL2013
	rename DT S2013

	rename DU PR2014
	rename DV CL2014
	rename DW S2014

	rename DX PR2015
	rename DY CL2015
	rename DZ S2015

	rename EA PR2016
	rename EB CL2016
	rename EC S2016

	rename ED PR2017
	rename EE CL2017
	rename EF S2017

	* Then I drop the year variable in observation 1 *
	drop in 1/3
	drop in 207/324
 
	rename A country

	* Next I finally am able to reshape the data to country-year *
	reshape long PR CL S, i(country) j(year)

	replace PR="" if PR==".."
	replace PR="" if PR=="-"
	replace PR="5" if PR=="2(5)"
	destring PR, replace

	replace CL="" if CL==".."
	replace CL="" if CL=="-"
	replace CL="6" if CL=="3(6)"
	destring CL, replace

	replace S="" if S==".."
	replace S="NF" if S=="F (NF)"

	replace S="0" if S=="NF"
	replace S="0" if S=="NF "
	replace S="1" if S=="PF"
	replace S="1" if S=="PF "
	replace S="2" if S=="F"
	replace S="2" if S=="F "
	replace S="" if S=="-"

	destring S, replace

	label define fh 0 "Not Free" 1 "Partially Free" 2 Free
	label values S fh

	label var PR "Freedom House Political Rights Scale"
	label var CL "Freedom House Civil Liberties Scale"
	rename S fhscore
	label var fhscore "Freedom House Score (Free,Partially Free, Not Free)"


		* Create COW country code *

		rename country Country
		run "cow.do"

		rename Country country
		tab country if ccode==0

		drop if ccode==0

		** EAST AND WEST GERMANY CORRECTION ***

		drop if ccode==255 & year<1990
		drop if ccode==265 & year>1989
		drop if ccode==260 & year>1989

		** USSR **

		drop if country=="USSR" & year>1990
		drop if country=="Russia" & year<1991

		** Yugoslavia **
		drop if country=="Yugoslavia" & year>1991
		drop if country=="Serbia" & year<2006
		drop if country=="Yugoslavia (Serbia & Montenegro)" & year<1992
		drop if country=="Yugoslavia (Serbia & Montenegro)" & year>2005

		** Vietnam **
		drop if country=="Vietnam, N." & year>1975
		drop if country=="Vietnam, S." & year>1975
		drop if country=="Vietnam" & year<1976

		** Yemen **
 
		** Czechoslovakia **
		 
		 order ccode
		 codebook ccode
		tab ccode if ccode==ccode[_n-1] & year==year[_n-1]
		browse if ccode==345
		
		browse if country=="Serbia & Montenegro"
		drop if country=="Serbia & Montenegro" & year<2004
		drop if country=="Serbia & Montenegro" & year>2005	
		 
		browse if ccode==352
		drop if country=="Cyprus" & PR==.
		drop if country=="Cyprus" & year<1992
		 sort ccode year
		 
		save "FH 2018.dta", replace
 
						
						********************
						** INSERTING 2019 **
						********************
						
		import excel "FH 2019.xlsx", ///
				sheet("Sheet1") firstrow   clear
		rename country Country
		run "cow.do"

		rename Country country
		tab country if ccode==0

		drop if ccode==0

			
		label var PR "Freedom House Political Rights Scale"
		label var CL "Freedom House Civil Liberties Scale"
		 
		label var fhscore "Freedom House Score (Free,Partially Free, Not Free)"

		merge 1:1 ccode year using "FH 2018.dta" 
		
		browse if year>2016
		sort country year
			 
		label values fh fh
		drop _merge
		order ccode
		
		browse if ccode==315
		
		drop if ccode==315 & year>1992
		
		browse if ccode==678
		drop if ccode==678 & year>1989	
		
		browse if ccode==680
		drop if ccode==680 & year>1989	
		
		save "FH 2019.dta", replace
		
		
		
		
		
		
